from flask import Blueprint, request,render_template
import time
import datetime

from .db import get_db
from .admin import login_required

status = Blueprint("status", __name__)

@status.route("/summary")
@login_required
def summary():
    conn = get_db()
    cur = conn.cursor()

    sql = """select
        count(distinct deviceid)
    from userinfo
    where
        deviceid != ''
        or  deviceid is not null
    """
    cur.execute(sql)
    total_dev = cur.fetchall()[0][0]

    sql = """select
        count(distinct terminalid)
    from userinfo
    where
        terminalid is not null
        or terminalid != ''
    """
    cur.execute(sql)
    total_terminal = cur.fetchall()[0][0]


    sql = """select
        count(distinct serial)
    from devinfo
    where
        lastupdate is not null
        or lastupdate != ''
    """
    cur.execute(sql)
    total_dev_on_cable = cur.fetchall()[0][0]

    sql = """select
        count(distinct terminalid)
    from mediatimes
    where
        videotime > 0
    """
    cur.execute(sql)
    total_user_on_mov = cur.fetchall()[0][0]

    sql = """select
        count(distinct terminalid)
    from mediatimes
    where
        musictime > 0
    """
    cur.execute(sql)
    total_user_on_music = cur.fetchall()[0][0]

    sql = """select
        count(distinct terminalid)
    from mediatimes
    where
        picturetime > 0
    """
    cur.execute(sql)
    total_user_on_picture = cur.fetchall()[0][0]

    sql = """select
        count(distinct terminalid)
    from mediatimes
    where
        documenttime > 0
    """
    cur.execute(sql)
    total_user_on_doc = cur.fetchall()[0][0]

    sql = """select
        count(distinct terminalid)
    from mediatimes
    where
        baiduliveness > 0
    """
    cur.execute(sql)
    total_user_on_bdyun = cur.fetchall()[0][0]

    cur.close()
    return render_template("status/index.html", **locals())

@status.route("/movie")
@login_required
def fav_mov():
    start = int(request.values.get("start", 0))
    count = 20
    conn = get_db()
    cur = conn.cursor()
    sql = """select
        videoname,count(videoname) as c
    from movienames
    group by videoname
    order by c desc
    limit %d,%d
    """ % (start, count)
    cur.execute(sql)
    movies = cur.fetchall()
    cur.close()
    return render_template("status/fav_mov.html", movies = movies, next=start + count)

@status.route("/terminal")
@login_required
def fav_terminal():
    start = int(request.values.get("start", 0))
    count = 20
    conn = get_db()
    cur = conn.cursor()

    sql = """select
        userinfo.terminaltype,count(userinfo.terminaltype) as t, terminalname.friendlyname
        from userinfo
            left join terminalname on userinfo.terminaltype = terminalname.terminalname
        group by userinfo.terminaltype
        order by t desc
        limit %d,%d
        """ % (start, count)
    cur.execute(sql)
    devs = cur.fetchall()
    cur.close()
    return render_template("status/fav_terminal.html", devs = devs, next = start + count)

@status.route("/day/detail")
@login_required
def every_day():
    date_from = request.values.get("date_from", "")
    date_to = request.values.get("date_to", "")
    if not date_from:
        d1 = datetime.datetime.now().replace(hour=0, minute=0, second=0, microsecond=0) - datetime.timedelta(1)
    else:
        d1 = datetime.datetime.strptime(date_from, "%Y-%m-%d")

    if not date_to:
        d2 = datetime.datetime.now().replace(hour=0, minute=0, second=0, microsecond=0) - datetime.timedelta(1)
    else:
        d2 = datetime.datetime.strptime(date_to, "%Y-%m-%d")

    start_time = min(d1, d2)
    end_time = max(d1, d2) + datetime.timedelta(hours = 23, minutes=59, seconds = 59)

    conn = get_db()
    cur = conn.cursor()

    sql = """select
        count(distinct terminalid)
    from mediatimes
    where
        last_update >= '%s' and last_update <= '%s'
    """ % (start_time, end_time)
    #print sql
    cur.execute(sql)
    active_user = cur.fetchall()[0][0]

    sql = """select
        count(distinct terminalid)
    from userinfo
    where
        createdate >= '%s' and createdate <= '%s'
    """ % (start_time, end_time)
    #print sql
    cur.execute(sql)
    new_user = cur.fetchall()[0][0]

    sql = """select
        count(distinct terminalid), sum(videotime)
    from mediatimes
        where videotime > 0  and recorddate >= '%s' and recorddate <= '%s'
    """ % (start_time.date(), end_time.date())
    cur.execute(sql)
    video_user,video_time = cur.fetchall()[0]

    sql = """select
        count(distinct terminalid), sum(musictime)
    from mediatimes
        where musictime > 0  and recorddate >= '%s' and recorddate <= '%s'
    """ % (start_time.date(), end_time.date())
    cur.execute(sql)
    music_user,music_time = cur.fetchall()[0]

    sql = """select
        count(distinct terminalid), sum(documenttime)
    from mediatimes
    where
        documenttime > 0  and recorddate >= '%s' and recorddate <= '%s'
    """ % (start_time.date(), end_time.date())
    cur.execute(sql)
    document_user,document_time = cur.fetchall()[0]

    sql = """select
        count(distinct terminalid), sum(picturetime)
    from mediatimes
    where
        picturetime > 0  and recorddate >= '%s' and recorddate <= '%s'
    """ % (start_time.date(), end_time.date())
    cur.execute(sql)
    picture_user,picture_time = cur.fetchall()[0]

    sql = """select
        count(distinct terminalid), sum(baiduliveness)
    from mediatimes
    where
        baiduliveness> 0  and recorddate >= '%s' and recorddate <= '%s'
    """ % (start_time.date(), end_time.date())
    cur.execute(sql)
    baidu_user,baidu_time = cur.fetchall()[0]

    sql = """select
        sum(startcount)
    from mediatimes
    where
        recorddate >= '%s' and recorddate <= '%s'
    """ % (start_time.date(), end_time.date())
    cur.execute(sql)
    start_count = cur.fetchall()[0][0]
    cur.close()

    return render_template("status/day.html", **locals())
